/***************************************************************************
B1. Backup database SLXNK
B2. Restore database SLXNK_HIS (neu can)
B3. Xoa du lieu tren database SLXNK
***************************************************************************/

--B1. Backup database SLXNK



/***************************************************************************
--B2. Restore database SLXNK_HIS
***************************************************************************/
IF EXISTS(SELECT [name] FROM sys.procedures WHERE [name]='RestoreDatabaseSLXNK')
DROP PROC RestoreDatabase
GO
----
CREATE PROCEDURE RestoreDatabaseSLXNK
    @backupFile varchar(500),
    @newDatabaseName varchar(100)
AS
	SET NOCOUNT ON
	-- // create a temp table to hold the file list for the db
	CREATE TABLE #fileList ( 
		LogicalName nvarchar(128),
		PhysicalName nvarchar(260),
		Type char(1),
		FileGroupName nvarchar(128),
		Size numeric(20,0),
		MaxSize numeric(20,0),
		FileID bigint,
		CreateLSN numeric(25,0),
		DropLSN numeric(25,0) NULL,
		UniqueID uniqueidentifier,
		ReadOnlyLSN numeric(25,0) NULL,
		ReadWriteLSN numeric(25,0) NULL,
		BackupSizeInBytes bigint,
		SourceBlockSize int,
		FileGroupID int,
		LogGroupGUID uniqueidentifier NULL,
		DifferentialBaseLSN numeric(25,0) NULL,
		
		DifferentialBaseGUID uniqueidentifier,
		IsReadOnly bit,
		IsPresent bit,
		TDEThumbprint bit )

	-- // Declare a variable to hold the restore statement
	DECLARE @RestoreStmt varchar(1000)
	-- // start the restore stmnt
	SET @RestoreStmt = '
		RESTORE DATABASE ' + @newDatabaseName + '
		FROM DISK = ''' + @backupFile + '''
		WITH
	'
	Declare @SetupDir varchar (200)
	SET @SetupDir = LEFT(@backupFile, LEN(@backupFile) - CHARINDEX('\', REVERSE(@backupFile))) + '\'
	print @SetupDir
		
	INSERT #fileList
	EXECUTE ('restore filelistonly from disk=''' + @backupFile + '''')

	--- // declare some vars to hold the filenames and types
	DECLARE @LogicalName nvarchar(128)
	DECLARE @PhysicalName nvarchar(260)
	DECLARE @Type char(1)
	DECLARE @LastSlash int

	DECLARE fileListCsr CURSOR FOR
		SELECT LogicalName, PhysicalName, Type
		FROM #fileList

	OPEN fileListCsr
	FETCH NEXT FROM fileListCsr INTO @LogicalName, @PhysicalName, @Type
	WHILE @@FETCH_STATUS = 0
	BEGIN
		-- // get the path from @PhysicalName
		SET @LastSlash = CHARINDEX('\', REVERSE(@PhysicalName))    
		SET @PhysicalName = @SetupDir + RIGHT(@PhysicalName, @LastSlash-1)		
		-- // update the file paths
		SET @RestoreStmt = @RestoreStmt + '
			MOVE ''' + @LogicalName + ''' TO ''' + @PhysicalName + ''',
		'	
		FETCH NEXT FROM fileListCsr INTO @LogicalName, @PhysicalName, @Type
	END
	-- // add stats
	SET @RestoreStmt = @RestoreStmt + ' STATS = 10'

	CLOSE fileListCsr
	DROP TABLE #fileList
	-- // execute the restore stmnt
	--print @RestoreStmt
	EXEC( @RestoreStmt )
GO
--test
EXEC RestoreDatabaseSLXNK 'E:\Test.Dua.Du.Lieu.Vao.History\SLXNK', 'SLXNK'
GO
--
DROP PROC RestoreDatabaseSLXNK
GO
/***************************************************************************
--B2. Ket thuc
***************************************************************************/

/***************************************************************************
--B3. Xoa du lieu tren database SLXNK
***************************************************************************/
IF EXISTS(SELECT [name] FROM sys.procedures WHERE [name]='DeleteTableByNamDK')
DROP PROC DeleteTableByNamDK
GO
----------
CREATE PROCEDURE [dbo].[DeleteTableByNamDK]
				@TableName  varchar(255)
				,@NamDK int
AS
DECLARE @SQLString nvarchar(1500);
DECLARE @ParmDefinition nvarchar(500);

/* Delete Table*/
SET @SQLString = 
	'DELETE FROM ' + @TableName +
	' WHERE NamDK = @_NamDK';
SET @ParmDefinition = N'@_NamDK int';
EXECUTE sp_executesql @SQLString, @ParmDefinition, 
						@_NamDK = @NamDK;					
GO
-----------------------------------------------------------------
ALTER PROCEDURE [dbo].[DeleteAllData]
AS
DECLARE @CurrentYear int;
SET @CurrentYear = 1900;
WHILE @CurrentYear < 2020--YEAR(GETDATE()) 
	BEGIN
		Exec [dbo].[DeleteTableByNamDK] 'DHangMD', @CurrentYear
		Exec [dbo].[DeleteTableByNamDK] 'DHangMDDK', @CurrentYear
		Exec [dbo].[DeleteTableByNamDK] 'DHangMDKH', @CurrentYear
		Exec [dbo].[DeleteTableByNamDK] 'DHangMDTH', @CurrentYear
		Exec [DeleteTableByNamDK] 'RR_GHINHANKQ_CT', @CurrentYear
		Exec [DeleteTableByNamDK] 'RR_GHINHANKQ', @CurrentYear
		Exec [DeleteTableByNamDK] 'DToKhaiMD', @CurrentYear
		SET @CurrentYear = @CurrentYear + 1
	END
GO
-------------------------------------------------------------
--test
EXEC DeleteAllData
GO
DROP PROC DeleteAllData
DROP PROC DeleteTableByNamDK
/***************************************************************************
--B3. Ket thuc
***************************************************************************/